-- STANDARD SCRIPT
-- STEP 1 BELOW HAS TO BE CUSTOMIZED FOR EACH CLIENT 
--


--------------------------------------------------------------------------------------
-- FUNCTION TO UPDATE BATCH TRANS TABLE



\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION trans_out_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_trans_t limit 1;
SELECT INTO v_cust_id cust_id from batch_trans_t limit 1;

PERFORM ml_upd_batch_status_trans_new_f(v_cust_id,v_batch_run_date,1,8,'trans_output_t INSERT');


-- STEP 1: SCRATCHING THE TABLE
TRUNCATE trans_output_t;



-- STEP 2: INSERT INTO trans_output_t;
RAISE NOTICE 'START INSERT IN trans_output_t TABLE ';

INSERT INTO trans_output_t
SELECT
  'C' as data_flag,
  final.cust_id ,
  final.hid     ,
  final.acctnum ,
  final.carrier_code,
  v_batch_run_date + interval '1 minute' as our_run_date,
  NULL as date_billed ,
  (CASE WHEN final.min_paid_date = '01/01/2500' THEN NULL
        ELSE                                     final.min_paid_date
        END) as paid_date,
  NULL as date_rejected,
  NULL as prior_ins_date,
  (CASE WHEN final.sum_pmt IS NOT NULL AND final.sum_pmt <> 0 THEN final.sum_pmt
        ELSE                                                       NULL
        END) as amount_paid,
  (CASE WHEN final.sum_pmt IS NOT NULL AND final.sum_pmt <> 0 THEN final.note
       WHEN final.ct_wk > 0                                   THEN 'WORKERS COMP'
       WHEN final.ct_auto > 0                                 THEN 'AUTO INSURANCE'
       WHEN final.ct_investigate > 0                          THEN 'PAYMENT TO MANUALLY INVESTIGATE'
       WHEN final.ct_denied_gen > 0                           THEN 'INSURANCE DENIAL - NO REASON GIVEN'
       ELSE                                                        final.note
       END) as note

FROM

       (SELECT

         DISTINCT ON (pmt.cust_id, pmt.hid, pmt.acctnum) *

       FROM

           (SELECT
             recon.*,
             trans.sum_pmt,
             trans.min_paid_date,
             trans.ct_wk,
             trans.ct_auto,
             trans.ct_denied_gen,
             trans.ct_investigate

            FROM

                (SELECT 
                      distinct on (cust_id,hid,acctnum,carrier_code) 
                      *,
                      (CASE WHEN medlytix_ins_type = 'D' THEN 1
                            ELSE                              0
                            END) as order_pmt
                 FROM
                     recon_t
                 WHERE
                     cust_id = v_cust_id
                 ORDER BY
                     cust_id, hid, acctnum, carrier_code, seqnum DESC
                ) as recon

                RIGHT JOIN

                (SELECT
                     cust_id,
                     hid,
                     acctnum,
                     sum(CASE WHEN medlytix_recon_flag = 'Y' THEN trans_amt ELSE 0 END)  as sum_pmt,
                     min(CASE WHEN medlytix_recon_flag = 'Y' THEN trans_date ELSE '01/01/2500' END) as min_paid_date,
                     sum(CASE WHEN trans_type_detail in ('W','ADJW') THEN 1  ELSE 0 END) as ct_wk,
                     sum(CASE WHEN trans_type_detail = 'AUTO' THEN 1 ELSE 0 END)         as ct_auto,
                     sum(CASE WHEN trans_type_detail = 'DEN01' THEN 1 ELSE 0 END)        as ct_denied_gen,
                     sum(CASE WHEN medlytix_recon_flag = 'P' THEN 1 ELSE 0 END)          as ct_investigate 
                 FROM
                     trans_t
                 WHERE
                     cust_id = v_cust_id AND medlytix_recon_date = v_batch_run_date
                 GROUP BY
                     cust_id, hid, acctnum
                ) as trans

                 ON
                 recon.cust_id = trans.cust_id AND recon.hid = trans.hid AND recon.acctnum = trans.acctnum

           ) as pmt

           WHERE (pmt.sum_pmt IS NOT NULL AND pmt.sum_pmt <> 0) OR 
                ( 
                  ( pmt.validate_status in ('VO','AO') or pmt.validate_status IS NULL ) 
                  AND 
                  ( ct_wk > 0 or ct_auto > 0 or ct_denied_gen > 0 or ct_investigate > 0)
                )
           ORDER by pmt.cust_id, pmt.hid, pmt.acctnum, pmt.order_pmt

       ) as final

      ;



RAISE NOTICE 'FINISH INSERT IN trans_output_t TABLE ';

PERFORM ml_upd_batch_status_trans_new_f(v_cust_id,v_batch_run_date,1,8,'FINISH INSERT IN trans_output_t');


  return v_status;
END;
$$ LANGUAGE plpgsql;
